Little Lemon need to create sales reports from the
data in their database. You can help them to produce their sales report by
querying their data. You can query their data
using virtual tables, joins, stored procedures
and prepared statements. Recap the basics of these tasks, and then see if you
can help Little Lemon. Little Lemon can query their database using
a virtual table. As you should know
from previous courses, a virtual table makes use of data that exists
in other tables. It doesn't physically
store any data. It's more like an interface that provides access to
data in the database. There are several
benefits to these tables. They simplify data
access and queries. They can be used to create a join from virtual
and base tables. You can use them to efficiently manipulate and filter data, and they support
database security. When creating virtual tables, you'll make use
of joins to build a view from multiple tables. Joins are used to
link records of data between one or more tables
based on a common column. You might use a join to
find information about a specific activity or
objects within the database. Or you might need to find where the relevant information
exists in more than one table. There are several
types of joins that you've explored
within these courses. These include inner join, left join, and right join. There's also the self-join
and the full outer join. You can make use of
these joins to query Little Lemon's database and retrieve the
information they need. You'll also need to help Little Lemon using
stored procedures. The main purpose of stored
procedures is to create reusable code that can be invoked and executed
efficiently. This makes your code
more consistent, re-usable, and easier
to use and maintain, so instead of typing the
same code repeatedly, you can save your
blocks of code as stored procedures that you can
then invoke when required. You can create as many
procedures as you need, and they can include
multiple parameters. Your code can also include
various types of SQL code. Just make sure each
one has a unique name. Remember that how you create a stored procedure depends on the task that you
need to achieve. You'll also assist in lemon with the use of prepared statements. Each time you create
SQL statements, they need to be
compiled and parsed by MySQL before they
can be executed. A more efficient method is to create a prepared
statement that only needs to be compiled ones and can then be used repeatedly. In other words, you can create a prepared statement that MySQL compilers and parses just
once before it's executed. So each time the
statement is invoked, MySQL knows that it's ready
to use and safe to execute. Prepared statements
are a much more efficient and optimal way of executing statements without using valuable MySQL resources. You should now be familiar with the techniques and methods that you can use to create a sales report for Little Lemon. If you need more information
on these topics, then remember that
you can review the learning material from
previous courses. Well done.